﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using EasyDentalMail.View;

namespace EasyDentalMail.DAO
{
    public class AgendaDAL
    {
        #region [private]

        private string connString = System.Configuration.ConfigurationManager.ConnectionStrings["edsConn"].ToString();

        private AgendaView montarDRAgenda(SqlDataReader dr)
        {
            AgendaView ag = new AgendaView();
            if (!dr.IsDBNull(0))
                ag.Id_Prestador = dr.GetInt32(0);
            if (!dr.IsDBNull(1))
                ag.Id_Unidade = dr.GetInt32(1);
            if (!dr.IsDBNull(2))
                ag.Data = dr.GetDateTime(2);
            if (!dr.IsDBNull(3))
                ag.HoraInicio = dr.GetInt32(3);
            if (!dr.IsDBNull(4))
                ag.HoraFim = dr.GetInt32(4);
            if (!dr.IsDBNull(5))
                ag.Telefone1 = dr.GetString(5);
            if (!dr.IsDBNull(6))
                ag.Telefone2 = dr.GetString(6);
            if (!dr.IsDBNull(7))
                ag.Telefone3 = dr.GetString(7);
            if (!dr.IsDBNull(8))
                ag.Endereco = dr.GetString(8);
            if (!dr.IsDBNull(9))
                ag.NomePai = dr.GetString(9);
            if (!dr.IsDBNull(10))
                ag.NomeMae = dr.GetString(10);
            if (!dr.IsDBNull(11))
                ag.Nome = dr.GetString(11);
            if (!dr.IsDBNull(12))
                ag.Email = dr.GetString(12);
            return ag;
        }

        private string montarSelectAgenda()
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("SELECT AGENDA.ID_PRESTADOR, AGENDA.ID_UNIDADE, AGENDA.DATA, AGENDA.HORAINICIO, AGENDA.HORAFIM, ");
            sb.Append("AGENDA.FONE1,AGENDA.FONE2,AGENDA.FONE3, PESSOAL.ENDRES, PESSOAL.NOMPAI, PESSOAL.NOMMAE, AGENDA.NOME, PESSOAL.EMAIL ");
            sb.Append("FROM AGENDA ");
            sb.Append("LEFT JOIN PESSOAL ON AGENDA.NROPAC = PESSOAL.NROPAC ");
            sb.Append("where DATA >= '{0}' ");
            sb.Append("and AGENDA.NOME <> '' and AGENDA.NOME is not NULL");
            return string.Format(sb.ToString(), DateTime.Today.ToString("yyyy-MM-dd 00:00:00"));
        }

        private string montarInsertAgenda()
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendLine("INSERT INTO AGENDA");
            sb.AppendLine("(ID_PRESTADOR,ID_UNIDADE,DATA,HORAINICIO,HORAFIM,SALA,TIPO,NOME,OBSERV,TIME_STAMP_INS,USER_STAMP_INS,PALM_UPD)");
            sb.AppendLine("VALUES");
            sb.AppendLine("({0},{1},'{2}',{3},{4},{5},{6},'{7}','{8}','{9}',{10},{11})");
            return sb.ToString();
        }

        #endregion

        #region [public]

        public List<AgendaView> getAgenda()
        {
            try
            {
                using (SqlConnection conn = new SqlConnection(connString))
                {
                    SqlCommand cmd = new SqlCommand();
                    cmd.CommandText = montarSelectAgenda();
                    cmd.Connection = conn;
                    conn.Open();
                    SqlDataReader dr = cmd.ExecuteReader();
                    List<AgendaView> lstAg = new List<AgendaView>();
                    while (dr.Read())
                    {
                        lstAg.Add(montarDRAgenda(dr));
                    }
                    conn.Close();
                    return lstAg;
                }
            }
            catch (Exception ex)
            {
                throw new Exception("Erro ao cadastrar GOOGLECALENDAR TABLE", ex);
            }
        }

        public void insert(AgendaView ag)
        {
            try
            {
                using (SqlConnection conn = new SqlConnection(connString))
                {
                    SqlCommand cmd = new SqlCommand();
                    cmd.CommandText = montarInsertAgenda();
                    cmd.Connection = conn;
                    conn.Open();
                    cmd.CommandText = string.Format(cmd.CommandText,ag.Id_Prestador,ag.Id_Unidade,ag.Data.ToString("yyyy-MM-dd"),ag.HoraInicio,ag.HoraFim,1,1,ag.Nome,ag.Observacao,DateTime.Now.ToString("yyyy-MM-dd"),1,1);
                    cmd.ExecuteNonQuery();
                    conn.Close();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        
    }
}
